﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Model.Orden;
using System.Collections;
using System.Data;
using System.Collections.ObjectModel;

namespace DataBase.Orden
{
    public static class DBOrden
    {
        static string connect = "Data Source=JCIDatabase.db.10200093.hostedresource.com; Initial Catalog=JCIDatabase; User ID=JCIDatabase; Password='ucbJCI2012!'";
        public static Order NroOrdenNoFueAsociada()
        {
            SqlConnection sql = null;
            Order orden = null;
            try
            {
                sql = new SqlConnection(connect);
                SqlCommand comando = sql.CreateCommand();
                comando.CommandText = "Select Nro,Fecha From Orden where Asociada = 'False'";
                sql.Open();
                SqlDataReader reader = comando.ExecuteReader();
                if (reader.HasRows)
                {

                    reader.Read();
                    orden = new Order();
                    orden.Nro = reader["Nro"].ToString();
                    orden.Fecha = DateTime.Parse(reader["Fecha"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sql != null)
                    sql.Dispose();
            }
            return orden;
        }
        public static DateTime FechaDeNumero(string nro)
        {
            SqlConnection conn = null;
            DateTime time = new DateTime();
            try
            {
                conn = new SqlConnection(connect);
                SqlCommand comando = conn.CreateCommand();
                comando.CommandText = "Select Fecha from Orden where Nro= @nro";
                comando.Parameters.AddWithValue("nro",nro);
                conn.Open();
                SqlDataReader reader = comando.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    time = DateTime.Parse(reader["Fecha"].ToString());
                }
                
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return time;
        }

        public static bool SetFecha(DateTime fecha, string Nro)
        {
            bool res = false;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection(connect);
                SqlCommand comando = conn.CreateCommand();
                comando.CommandText = "UPDATE Orden SET Fecha=@fecha where Nro = @nro";
                comando.Parameters.AddWithValue("fecha", fecha);
                comando.Parameters.AddWithValue("nro", Nro);
                conn.Open();
                comando.ExecuteReader();
                res = true;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return res;
        
        }
        public static bool setAsociada(string nro)
        {
            bool res = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connect);
                SqlCommand comando = conn.CreateCommand();
                comando.CommandText = "UPDATE Orden SET Asociada='True' where Nro = @nro";
                comando.Parameters.AddWithValue("nro",nro);
                conn.Open();
                int i = comando.ExecuteNonQuery();
                if (i > 0)
                    res = true;
                else
                    res = false;
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            return res;
        }
        public static bool ExisteOrdenEnEsaFecha(DateTime time)
        {
            bool res = false;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection(connect);
                SqlCommand comando = conn.CreateCommand();
                comando.CommandText = "Select * from Orden where Fecha = @fecha";
                comando.Parameters.AddWithValue("fecha",time);
                conn.Open();
                SqlDataReader reader = comando.ExecuteReader();
                if (reader.HasRows)
                {
                    res = true;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (conn!=null)
                {
                    conn.Dispose();
                }
            }
            return res;
        }

        public static bool EstaAprobada(string nro)
        {
            bool res = true;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection(connect);
                SqlCommand comando = conn.CreateCommand();
                comando.CommandText = "Select Aprobada From Orden Where Nro=@nro";
                comando.Parameters.AddWithValue("nro",nro);
                conn.Open();
                SqlDataReader reader=  comando.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    res = (bool)reader["Aprobada"];
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return res;
        }
        
        public static bool Aprobar(string nro)
        {
            SqlConnection conn = null;
            bool res = true;
            try
            {
                conn = new SqlConnection(connect);
                SqlCommand comando = conn.CreateCommand();
                comando.CommandText = "UPDATE Orden SET Aprobada='True' where Nro = @nro";
                comando.Parameters.AddWithValue("nro",nro);
                conn.Open();
                int i = comando.ExecuteNonQuery();
                if (i > 0)
                    res = true;
                else
                    res = false;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return res;
        }

        public static Order ExisteOrdenSinAprobar()
        {
            SqlConnection conn = null;
            Order orden = null;
            try
            {
                conn = new SqlConnection(connect);
                SqlCommand comando = conn.CreateCommand();
                comando.CommandText = "Select * From Orden where Aprobada = 'False'";
                conn.Open();
                SqlDataReader reader = comando.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    orden = new Order();
                    orden.Aprobada = (bool)reader["Aprobada"];
                    orden.Asociada = (bool)reader["Asociada"];
                    orden.Fecha = DateTime.Parse(reader["Fecha"].ToString());
                    orden.FechaUltimaModificacion = DateTime.Parse(reader["FechaUltimaModificacion"].ToString());
                    orden.Nro = reader["Nro"].ToString();
                    orden.Secre = false;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return orden;
        }
        public static Order buscarOrdenNumero(string nro)
        {
            Order orden = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connect);
                SqlCommand comm = conn.CreateCommand();
                comm.CommandText = "SELECT * FROM Orden WHERE Nro=@nro";
                conn.Open();
                SqlDataReader reader = comm.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    orden = new Order();
                    orden.Aprobada = (bool)reader["Aprobada"];
                    orden.Asociada = (bool)reader["Asociada"];
                    orden.Fecha = DateTime.Parse(reader["Fecha"].ToString());
                    orden.FechaUltimaModificacion = DateTime.Parse(reader["FechaUltimaModificacion"].ToString());
                    orden.Nro = reader["Nro"].ToString();
                }
            }
            catch (Exception e)
            {
                return orden;
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return orden;
        }

        public static Collection<Order> buscarOrdenNro(string Nro,bool Secre)
        {
            SqlConnection conn = null;
            Collection<Order> ordenes = null;
            try
            {
                conn = new SqlConnection(connect);
                SqlCommand comando = conn.CreateCommand();
                comando.CommandText = "Select * from Orden where Nro = @nro";
                comando.Parameters.AddWithValue("nro", Nro);
                conn.Open();
                SqlDataReader reader = comando.ExecuteReader();
                ordenes = new Collection<Order>();
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        Order orden = new Order();
                        orden.Aprobada = (bool)reader["Aprobada"];
                        orden.Asociada = (bool)reader["Asociada"];
                        orden.Fecha = DateTime.Parse(reader["Fecha"].ToString());
                        orden.FechaUltimaModificacion = DateTime.Parse(reader["FechaUltimaModificacion"].ToString());
                        orden.Nro = reader["Nro"].ToString();
                        orden.Secre = Secre;
                        ordenes.Add(orden);
                    }
                }
            }
            catch (Exception e)
            {

            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return ordenes;
        }
        public static Collection<Order> buscarOrdenFecha(DateTime timeIni, DateTime timeFin,bool secre)
        {
            SqlConnection conn = null;
            Collection<Order> ordenes = null;
            try
            {
                conn = new SqlConnection(connect);
                ordenes = new Collection<Order>();
                conn = new SqlConnection(connect);
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "Select * from Orden where fecha>=@timeIni and fecha<=@timeFin";
                
                command.Parameters.AddWithValue("timeIni",timeIni);
                command.Parameters.AddWithValue("timeFin",timeFin);
                conn.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        Order orden = new Order();
                        orden.Aprobada = (bool)reader["Aprobada"];
                        orden.Asociada = (bool)reader["Asociada"];
                        orden.Fecha = DateTime.Parse(reader["Fecha"].ToString());
                        orden.FechaUltimaModificacion = DateTime.Parse(reader["FechaUltimaModificacion"].ToString());
                        orden.Nro = reader["Nro"].ToString();
                        orden.Secre = secre;
                        ordenes.Add(orden);
                    }
                   
                }
            }
            catch (Exception e)
            {

            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return ordenes;
        }

        public static bool canCreateOrder()
        {
            bool answer = true;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connect);
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT Aprobada FROM Orden WHERE Aprobada='False'";
                conn.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                    answer = false;
                else
                    answer = true;

            }
            catch (Exception e)
            {
                answer = false;
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return answer;
        }
        public static int returnStatus(bool answer)
        {
            if (answer)
                return 1;
            else return 0;

        }
        public static string lastNro()
        {
            string result = "";
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connect);
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "Select Nro From Orden where [index] in ( SELECT max([Index])as a FROM Orden )";
                conn.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    result = reader["Nro"].ToString();
                    
                }
            }
            catch (Exception e)
            { 
            
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return result;
        }
        public static bool insertOrder(Order orden, List<Model.Orden.Punto> punto)
        {
            bool answer = true;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connect);
                SqlCommand command = conn.CreateCommand();
                command.CommandText = "INSERT INTO Orden (Nro,Aprobada,Fecha,FechaUltimaModificacion,Asociada) VALUES(@nro,@aprobada,@fecha,@fechaUltimaModificacion,@asociada)";
                
                command.Parameters.AddWithValue("nro", orden.Nro);
                command.Parameters.AddWithValue("aprobada", false);
                command.Parameters.AddWithValue("fecha", orden.Fecha);
                command.Parameters.AddWithValue("fechaUltimaModificacion", orden.FechaUltimaModificacion);
                command.Parameters.AddWithValue("asociada",orden.Asociada);
                conn.Open();
                command.ExecuteNonQuery();
                for (int i = 0; i < punto.Count; i++)
                {
                    SqlCommand command2 = conn.CreateCommand();
                    command2.CommandText = "INSERT INTO Punto (Nro,Punto,NombreDePunto) VALUES(@nro,@punto,@nombreDePunto)";
                    command2.Parameters.AddWithValue("nro", punto[i].Nro);
                    command2.Parameters.AddWithValue("punto", punto[i].NumeroPunto);
                    command2.Parameters.AddWithValue("nombreDePunto", punto[i].NombreDePunto);
                    command2.ExecuteNonQuery();
                }
                    

            }
            catch (Exception e)
            {
                answer = false;
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return answer;
        }
    }
}
